/*准备工作：创建表空间和临时表空间，用户绑定，权限，根据超市管理系统需求excel提
供内容建表约束及插入测试数据*/

create table t_dic(
type varchar2(10),
name varchar2(20),
key varchar2(10) unique,
value varchar2(20) not null)

create table goods(
id varchar2(32) primary key,
type varchar2(10),
constraint type foreign key(type) references t_dic(key),
name varchar2(100),
inprice	number(10,2),
outprice	number(10,2),
tjdw	varchar2(10),
constraint tjdw foreign key(tjdw) references t_dic(key),
qualitydate	number(4) check (qualitydate > 0))

create table stock(
id	varchar2(32) primary key,
goodsid	varchar2(32),
constraint goodsid foreign key(goodsid) references goods(id),
sl	number(4),
intime	date default sysdate,
productime	date)

create table income(
id	varchar2(32) primary key,
goodsid_1	varchar2(32),
constraint goodsid_1 foreign key(goodsid_1) references goods(id),
stockid_1	varchar2(32),
constraint stockid_1 foreign key(stockid_1) references stock(id),
sl number(4) check (sl>0),
discount	number(4,1),
allprice	number(8,1),
outtime	date)

create table outcome(
id	varchar2(32) primary key,
goodsid_2	varchar2(32),
constraint goodsid_2 foreign key(goodsid_2) references goods(id),
stockid_2	varchar2(32),
constraint stockid_2 foreign key(stockid_2) references stock(id),
sl	number(4,1) check (sl>0), 
allprice	number(8,1),
intime	date)

--1. 在货物库存表的goodsid建立索引
create index ind_goodsid on stock(goodsid) 

--2. 在货物类型表的货物名称列添加唯一约束
Alter table goods add constraint name unique(name)

--3. 将货物类型表的各种物品，在库存表插入一条记录，要求每个字段都填写完整，保证数量都在100以上。
create sequence d start with 1 increment by 1 
insert into stock(id,goodsid,sl,intime,productime)
select d.nextval,goods.id,150,sysdate,sysdate-80 from goods

drop sequence d
delete from stock
drop table stock
select * from stock

--4. 查询出货物表中过期的所有物品
select g.id,g.name,type from goods g join stock s on g.id=s.goodsid 
where sysdate-productime>qualitydate group by g.id,g.name,type

--5. 查询货物表中10天之内过期的所有物品
select g.id,g.name,type from goods g join stock s on g.id=s.goodsid
where qualitydate-(sysdate-productime) between 0 and 10

--6. 于当前时间新进货脉动50瓶，可口可乐50瓶，在出账表和库存表插入相应记录


--7. 于当前时间卖出泰山一包，在进账表插入相应记录，在库存表修改相应记录


--8. 查询库存内所有物品的总价，进货价计算
select g.id,name,sum(s.sl*inprice) 总价 from goods g join stock s on s.goodsid=g.id  group by g.id,name

--9. 查询酒水类物品的仓库保有量最大的物品id和总价
select g.id,sum(s.sl*inprice) from goods g join t_dic t on g.type=t.key
join stock s on s.goodsid=g.id where key='g02' group by g.id


--10. 查询价格大于5元低于10元的所有物品名称
select name from goods where outprice between 5 and 10

--11. 查询名称中有山字的所有物品
select * from goods where name like '%山%'

--12. 查询进货日期是今天的所有进货记录
select * from outcome where trunc(intime) = trunc(sysdate)

--13. 查询名称长度最大的物品名称
select name from (
select name,rank()over(order by length(name) desc) r from goods) where r=1 

--14. 汇总各类型物品的库存，其中烟和酒水单独统计，其他物品展示为其他
select 
 sum (case when key='g01' then sl end) 烟类,
 sum (case when key='g02' then sl end) 酒水类,
 sum (case when key not in('g01','g02') then sl end) 其他类
from(
select * from goods g join t_dic t on g.type=t.key
join stock s on s.goodsid=g.id) where key in('g01','g02','g03','g04','g05','g06')

--15. 更新可比克薯片的名称，将名称改为可比克薯片（袋装）
update goods set name='可比克薯片（袋装）'  where name='可比克薯片'

--16. 将各类物品根据价格倒序排列展示
select * from goods order by outprice desc

--17. 查询出今天的销售额，毛利润


--18. 根据每种物品类型（类型包括很多物品）昨天销售的累计数量，求出最大的数量的类型名称及总数量


--19. 查询出烟类，近10天的销售情况，展示出物品名称，卖出数量，总价格，毛利润


--20. 根据天数分组，汇总近10天每天的销售情况，毛利润和总价格


--21. 创建视图，展示当月销售物品中，分类统计各类物品的销售数量，总进价，总卖出价，毛利润


--22. 在物品表里查出利润率最大的物品


--23. 因为某人讲价，于当前时间，以8折卖出馒头50个，在进账表和库存表做出相应修改
